🗄️ ER Diagram · Subscribe Web · v3.0

Subscribe Web · Database Schema + ER Diagram

15 tables · relationships · sample data · APIs · Everything needed to implement the 6-step Subscribe wizard on the backend
📅 Date: 13 ก.ค. 2569 (2026-07-13) 👤 Owner: วุธ (PM/SA) 📄 Source: ConstructQ_Subscribe.html
15
Tables
4
Packages
9
Features (F1-F9)
6
Roles
11
APIs

01Overview · Table Groups

15 tables grouped into 5 domains

📌
Scope: Tables derived from Subscribe screen (6-step wizard) · covers T&C consent · package/subscription · company (organization) · users + roles + permissions · payment flow (Omise + SlipOK + Bank) · invoice · tambon master.
GroupTablesPurpose
Auth & Consentterms_versions, terms_articles, pdpa_versions, pdpa_consentsStore T&C + PDPA versions + user acceptance log
Packagepackages, features, package_features4 subscription tiers + 9 features with M-M mapping
Organizationorganizations, subscriptionsCompany profile + active subscription details
Users & RBACusers, roles, user_roles, role_permissionsUsers invited from Step 04 + role assignment + module-level permission matrix
Paymentpayments, payment_slips, invoices, tambon_masterPayment transactions (3 methods) + slip verification + invoice + address autocomplete

02ER Diagram · Visual Overview

Boxes = tables · Lines = FK relationships · Grouped by color = domain

Subscribe Web · Entity-Relationship Diagram 15 tables · 5 domain groups · one-to-many + many-to-many relationships 🔒 AUTH & CONSENT terms_versions 🔑 id version_no effective_at terms_articles 🔑 id 🔗 version_id article_no · title · body pdpa_versions 🔑 id version_no · body pdpa_consents 🔑 id 🔗 user_id · tc_ver · pdpa_ver accepted_at · ip · device 1:N 💎 PACKAGE packages 🔑 id code (trial/starter/pro/ent) name · subtitle_th · en price_monthly · price_yearly user_quota · project_quota is_free · is_popular package_features 🔑 id 🔗 package_id 🔗 feature_id limit_value · notes features (F1-F9) 🔑 id code · name · phase 1:N N:1 🏢 ORGANIZATION organizations 🔑 id name · tax_id (13 digit) address_line1 · line2 tambon · district · province postal_code · country contact_name · phone · email logo_url ⚡ business_type created_at · updated_at subscriptions 🔑 id 🔗 org_id 🔗 package_id cycle (monthly/yearly) duration · start_date end_date · status total_price · vat_amount is_trial · trial_ends_at created_at 1:N 👥 USERS & RBAC users 🔑 id 🔗 org_id name · email (unique) phone · password_hash status (pending/active) invited_at · invite_token first_login_at avatar_url · locale created_at · updated_at user_roles 🔑 id 🔗 user_id 🔗 role_id assigned_at assigned_by is_primary roles (6 codes) 🔑 id code (org_admin, pm, qc_mgr, qc_insp, sub, ceo) name_th · name_en description_th · en is_system_role role_permissions 🔗 role_id · module_code · can_view/create/edit/approve/delete 1:N N:1 1:N 💳 PAYMENT payments 🔑 id 🔗 subscription_id 🔗 org_id method (card/qr/bank) amount · vat · total status (pending/paid/failed) omise_charge_id card_last4 · card_brand paid_at · created_at payment_slips 🔑 id 🔗 payment_id slip_url · slip_type slipok_ref verified · verified_at invoices 🔑 id 🔗 payment_id invoice_no tax_invoice_no pdf_url · sent_at tambon_master (autocomplete) 🔑 id · tambon_th/en · district_th/en · province_th/en · postal_code 1:N 1:1 org_id (users) subscription_id (payments) package_id (subscriptions) user_id (consents) tambon (ref) Legend Auth & Consent (4) Package (3) Organization (2) Users & RBAC (4) Payment + Master (4) Relationship Cardinality Notation → (solid) = required FK (NOT NULL) · - - (dashed) = optional or reference-only 1:N = one to many · N:1 = many to one · 1:1 = one to one · N:N = many to many (via junction) 💡 Data Flow (6-step wizard) Step 01 T&C: terms_versions + pdpa_versions read → pdpa_consents insert (after both checkboxes) Step 02 Package: packages + package_features + features read (display) Step 03 Company: organizations insert · tambon_master lookup for address autocomplete Step 04 Users: users + user_roles insert · roles + role_permissions read (permission matrix) Step 05 Payment: subscriptions insert · payments insert · payment_slips upload → invoices generate Step 06 Welcome: Read-back all data · trigger invitation emails · activate subscriptions.status=active
Figure 1 · Subscribe Web ER Diagram · 15 tables · 5 domain groups · data flows top-down through 6 wizard steps

03Sample Data (from ConstructQ_Subscribe.html)

Extracted directly from window.SUB_PACKAGES · window.SUB_FEATURES · window.SUB_ROLES · window.SUB_BANK

💎 packages (4 rows)

idcodenameprice_monthlyprice_yearlyuser_quotaproject_quotais_popular
1trialTrial0031false
2starterStarter3,90042,900103false
3proProfessional7,90086,9003015true
4enterpriseEnterpriseNULL (contact)NULL99999999false

🧩 features (9 rows · F1-F9)

idcodephasename_enname_th
1F11Project & Zone SetupProject & Zone Setup
2F21Drawing & Doc MgmtDrawing & Doc Mgmt
3F32Checklist BuilderChecklist Builder
4F42Inspection ScheduleInspection Schedule
5F53Mobile Field InspectionMobile Field Inspection
6F63NCR ManagementNCR Management
7F73Photo & Evidence MgmtPhoto & Evidence Mgmt
8F84Auto Report GenerationAuto Report Generation
9F94Quality DashboardQuality Dashboard

👥 roles (6 rows)

idcodename_thname_endescription_en
1org_adminผู้ดูแลระบบบริษัทฯOrganize AdminOrganization administrator, manages users and org settings
2pmผู้จัดการโครงการProject ManagerManages projects, oversees resources and reporting
3qc_mgrผู้จัดการตรวจสอบคุณภาพงานQC ManagerQC head, approves ITP plans and NCRs
4qc_inspผู้ตรวจสอบคุณภาพงานQC (Inspector)Field inspector, fills checklists and raises NCRs
5subผู้รับเหมา / ผู้รับเหมาช่วงOP/Sub ContractorSub-contractor/foreman, handles NCR rework
6ceoผู้บริหารCEOExecutive, views overall dashboard

🏦 Bank Info (const · displayed in Step 05 Bank Transfer)

FieldValue
bank_nameธนาคารกสิกรไทย / Kasikorn Bank
bank_branchสำนักงานใหญ่ / Headquarters
account_nameบริษัท วันโซลูชั่น อีอาร์พี จำกัด / One Solution ERP Co., Ltd.
account_no027-3-89456-1
💡
Full sample data set: Includes 4 packages · 9 features · 44 package_features (mapping) · 6 roles · 144 role_permissions (24 modules × 6 roles) · 1 T&C version · 11 T&C articles · 1 PDPA version · 1 sample organization + 3 sample users · 1 sample subscription + payment + invoice · 500+ tambon rows (Thai postal master). See seed script: seed_subscribe_v3.0.py

04APIs Mapped to Tables

11 endpoints · Go-Chi v5 handlers · tables touched per endpoint

# Endpoint Handler Code Method Tables Touched Purpose
1/api/terms/latestsubscribe.GetLatestTermsGETterms_versions · terms_articlesFetch latest T&C + all articles (Step 01)
2/api/pdpa/latestsubscribe.GetLatestPDPAGETpdpa_versionsFetch latest PDPA policy body
3/api/consents/acceptsubscribe.PostConsentPOSTpdpa_consentsInsert consent row after T&C + PDPA checkbox tick
4/api/packagessubscribe.ListPackagesGETpackages · package_features · featuresList all 4 packages with features (Step 02)
5/api/tambon?q=subscribe.SearchTambonGETtambon_masterAutocomplete search for Step 03 address
6/api/uploads/logosubscribe.UploadLogoPOST (multipart)files (S3) · organizations.logo_url updatedCompany logo upload (Step 03)
7/api/rolessubscribe.ListRolesGETroles · role_permissionsList 6 roles + permission matrix (Step 04)
8/api/subscriptions/registersubscribe.RegisterSubscriptionPOST (tx)organizations · users · user_roles · subscriptionsAtomic multi-insert: org + users + subscription (Step 05 pre-payment)
9/api/payments/omise/chargesubscribe.OmiseChargePOSTpayments (Omise integration)Credit card charge via Omise API
10/api/payments/slipok/verifysubscribe.SlipOKVerifyPOST (multipart)payment_slips · paymentsUpload slip + SlipOK verify (PromptPay or Bank)
11/api/users/invite-batchsubscribe.InviteUsersBatchPOSTusers · notifications (email queue)Send invitation emails after payment (Step 06)

🔐 Error Codes (bilingual TH+EN)

CodeHTTPTH MessageEN Message
SUB_001400เลขประจำตัวผู้เสียภาษีต้อง 13 หลักTax ID must be 13 digits
SUB_002400รูปแบบอีเมลไม่ถูกต้องInvalid email format
SUB_003409อีเมลนี้ถูกใช้แล้วEmail already exists
SUB_004422รหัสผ่านต้องมีอย่างน้อย 8 ตัวอักษรPassword must be at least 8 characters
SUB_005402บัตรถูกปฏิเสธCard was declined
SUB_006422ยอดโอนไม่ตรงกับใบเสร็จTransfer amount does not match invoice
SUB_007413ไฟล์ใหญ่เกิน 5 MBFile size exceeds 5 MB
SUB_008415รองรับเฉพาะ PNG/JPG/SVGOnly PNG/JPG/SVG supported
SUB_009422จำนวนผู้ใช้งานเกินโควตาแพ็คเก็จUser count exceeds package quota
SUB_010400ต้องยอมรับข้อกำหนดและ PDPA ก่อนMust accept both T&C and PDPA

05Table Details · Field-by-field

All 15 tables with columns, types, keys

📜

terms_versions

Auth & Consent
Master versions of Terms & Conditions · effective-date driven · immutable per version
ColumnTypeKeyNote
iduuidPKPrimary key
version_novarchar(20)UNIQUEe.g. "1.0"
effective_attimestamptzWhen this version takes effect
published_byuuidFK usersAdmin who published
is_currentbooleanOnly one row true at a time
created_attimestamptz
📝

terms_articles

Auth & Consent
11 articles per T&C version (นิยาม, การสมัคร, บริการ, ค่าธรรมเนียม, IP, PDPA, SLA, ความรับผิด, ยกเลิก, การเปลี่ยนแปลง, ข้อพิพาท)
ColumnTypeKeyNote
iduuidPK
version_iduuidFK terms_versions
article_noint1-11
title_thtext
title_entext
body_thtextJSON array of paragraphs
body_entext
💎

packages

Package
4 subscription tiers: Trial (free) · Starter · Professional (popular) · Enterprise (contact sales)
ColumnTypeKeyNote
iduuidPK
codevarchar(20)UNIQUEtrial | starter | pro | enterprise
namevarchar(100)Display name
eyebrow_th, eyebrow_envarchar(50)Small header
subtitle_th, subtitle_entext
price_monthlynumeric(12,2)NULL for Enterprise
price_yearlynumeric(12,2)NULL for Enterprise
yearly_savingsnumeric(12,2)
user_quotaintMax users
project_quotaintMax concurrent projects
is_freebooleanTrial=true
is_popularbooleanShow "Most Popular" badge
is_contactbooleanEnterprise=true
bullets_th, bullets_enjsonbArray of feature bullets
display_orderint1, 2, 3, 4
is_activeboolean
🧩

features

Package
9 core features F1-F9 · organized into 4 phases (Setup / Plan / Field / Report)
ColumnTypeKeyNote
iduuidPK
codevarchar(10)UNIQUEF1..F9
phaseint1-4
name_th, name_envarchar(100)
desc_th, desc_entext
is_activeboolean
🔗

package_features

Package
Many-to-many junction · defines which features are included per package + quotas
ColumnTypeKeyNote
iduuidPK
package_iduuidFK packages
feature_iduuidFK features
limit_valueinte.g. AI Photo Analysis 200/mo
limit_unitvarchar(20)per_month · total · unlimited
notes_th, notes_entext
🏢

organizations

Organization
Company profile from Step 03 · tax ID 13 digits · tambon-verified address
ColumnTypeKeyNote
iduuidPK
namevarchar(200)ชื่อบริษัท
tax_idvarchar(13)UNIQUE13-digit Thai tax
business_typevarchar(50)construction · developer · consultant
address_line1varchar(200)
address_line2varchar(200)
tambonvarchar(100)ตำบล/แขวง
districtvarchar(100)อำเภอ/เขต · from tambon lookup
provincevarchar(100)จังหวัด · from tambon lookup
postal_codevarchar(10)from tambon lookup
countryvarchar(50)Thailand default
contact_namevarchar(100)
contact_phonevarchar(20)
contact_emailvarchar(150)
logo_urltextS3 path
created_at, updated_attimestamptz
📋

subscriptions

Organization
Active subscription · one row per organization at a time · handles Trial + paid
ColumnTypeKeyNote
iduuidPK
org_iduuidFK organizations
package_iduuidFK packages
cyclevarchar(10)monthly · yearly
durationintNumber of cycles
start_date, end_datedate
statusvarchar(20)pending · active · expired · cancelled
unit_pricenumeric(12,2)
subtotal, vat_amount, total_pricenumeric(12,2)VAT 7%
is_trialboolean
trial_ends_attimestamptzTrial=30 days
auto_renewboolean
👤

users

Users & RBAC
Invited users from Step 04 · sent invitation email · sets first password on first login
ColumnTypeKeyNote
iduuidPK
org_iduuidFK organizations
namevarchar(100)
emailvarchar(150)UNIQUE(org_id, email)
phonevarchar(20)
password_hashvarchar(255)bcrypt · null until first login
statusvarchar(20)pending · active · disabled
invite_tokenvarchar(64)1-time use · 7-day expiry
invited_at, first_login_attimestamptz
avatar_urltext
localevarchar(10)th_TH · en_US
💳

payments

Payment
Payment transactions · 3 methods: card (Omise), qr (PromptPay+SlipOK), bank (transfer+SlipOK)
ColumnTypeKeyNote
iduuidPK
subscription_iduuidFK subscriptions
org_iduuidFK organizationsDenorm for query speed
methodvarchar(10)card · qr · bank
amount, vat_amount, total_amountnumeric(12,2)
statusvarchar(20)pending · paid · failed · refunded
omise_charge_idvarchar(100)chrg_test_xxx
card_last4varchar(4)
card_brandvarchar(20)visa · mastercard · jcb
failure_reasontext
paid_attimestamptz
📄

invoices

Payment
Tax invoice per successful payment · numbering per Thai Revenue Code §86
ColumnTypeKeyNote
iduuidPK
payment_iduuidFK payments · UNIQUE1:1
invoice_novarchar(50)UNIQUEINV-2026-000001
tax_invoice_novarchar(50)UNIQUETAX-2026-000001
pdf_urltextS3 path
issued_at, sent_attimestamptz
🗺️

tambon_master

Payment / Master
Thai postal master · seed with 77 provinces · ~7,500 tambon · used by Step 03 autocomplete
ColumnTypeKeyNote
idserialPK
tambon_codevarchar(10)UNIQUEGovernment tambon code
tambon_th, tambon_envarchar(100)
district_th, district_envarchar(100)
province_th, province_envarchar(100)
postal_codevarchar(10)
📌
Remaining tables: pdpa_versions · pdpa_consents · roles · user_roles · role_permissions · payment_slips — full schemas in the Excel companion (ConstructQ_Subscribe_Tables_v3.0.xlsx)

06Seed Script Overview

seed_subscribe_v3.0.py · subscribe_seed_v3.0.sql

🐍
Sample data seed: Python script imports ALL data extracted from ConstructQ_Subscribe.html:
  • packages (4) — Trial · Starter · Professional · Enterprise · exact prices + quotas + bullets
  • features (9) — F1-F9 with phases 1-4 · TH + EN names + descriptions
  • package_features (30+ rows) — Feature availability per package
  • roles (6) — org_admin · pm · qc_mgr · qc_insp · sub · ceo
  • role_permissions (144 rows) — 24 modules × 6 roles matrix from window.UM_PERMISSIONS
  • terms_versions + terms_articles (11) — 1 v1.0 T&C + 11 articles TH/EN
  • pdpa_versions (1) — v1.0 PDPA
  • tambon_master (~50 popular tambon) — Bangkok districts as starter set (full 7,500 available via separate script)
  • Sample organization + 3 users + 1 subscription (Trial) — For dev testing

▶️ Run the Seed

  1. Prerequisites: PostgreSQL 15+ · schema migrated · psycopg2 or asyncpg installed
  2. Set env: DATABASE_URL=postgresql://user:pass@localhost:5432/constructq
  3. Run: python seed_subscribe_v3.0.py
  4. Or execute SQL directly: psql $DATABASE_URL < subscribe_seed_v3.0.sql
  5. Verify: SELECT COUNT(*) FROM packages; should return 4

📁 Files Delivered

  • ConstructQ_Subscribe_ER_v3.0.html — this file · ER diagram + table catalog
  • ConstructQ_Subscribe_Tables_v3.0.xlsx — 4 sheets: Tables · Fields · Sample Data · APIs
  • seed_subscribe_v3.0.py — Python seed script · idempotent · ON CONFLICT DO NOTHING
  • subscribe_seed_v3.0.sql — Pure SQL alternative
  • subscribe_sample_data_v3.0.json — Raw JSON extracted from window.SUB_* variables